/*
 Navicat Premium Data Transfer

 Source Server         : 47.96.114.188
 Source Server Type    : SQL Server
 Source Server Version : 14003076
 Source Host           : 47.96.114.188:1433
 Source Catalog        : springboot-demo
 Source Schema         : dbo

 Target Server Type    : SQL Server
 Target Server Version : 14003076
 File Encoding         : 65001

 Date: 05/05/2019 15:39:55
*/


-- ----------------------------
-- Table structure for sys_permission
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[sys_permission]') AND type IN ('U'))
	DROP TABLE [dbo].[sys_permission]
GO

CREATE TABLE [dbo].[sys_permission] (
  [id] int IDENTITY(1,1) NOT NULL,
  [name] nvarchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
  [url] nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
  [description] nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
  [lft] int NOT NULL,
  [rgt] int NOT NULL
)
GO

ALTER TABLE [dbo].[sys_permission] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Records of [sys_permission]
-- ----------------------------
SET IDENTITY_INSERT [dbo].[sys_permission] ON
GO

INSERT INTO [dbo].[sys_permission] ([id], [name], [url], [description], [lft], [rgt]) VALUES (N'5', N'root', N'/', N'根节点', N'1', N'8')
GO

INSERT INTO [dbo].[sys_permission] ([id], [name], [url], [description], [lft], [rgt]) VALUES (N'6', N'test', N'/test', N'测试', N'2', N'7')
GO

INSERT INTO [dbo].[sys_permission] ([id], [name], [url], [description], [lft], [rgt]) VALUES (N'7', N'test-admin', N'/admin', N'管理员权限测试', N'3', N'4')
GO

INSERT INTO [dbo].[sys_permission] ([id], [name], [url], [description], [lft], [rgt]) VALUES (N'8', N'test-user', N'/user', N'用户权限测试', N'5', N'6')
GO

SET IDENTITY_INSERT [dbo].[sys_permission] OFF
GO


-- ----------------------------
-- Table structure for sys_permission_role
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[sys_permission_role]') AND type IN ('U'))
	DROP TABLE [dbo].[sys_permission_role]
GO

CREATE TABLE [dbo].[sys_permission_role] (
  [id] int IDENTITY(1,1) NOT NULL,
  [role_id] int NOT NULL,
  [permission_id] int NOT NULL
)
GO

ALTER TABLE [dbo].[sys_permission_role] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Records of [sys_permission_role]
-- ----------------------------
SET IDENTITY_INSERT [dbo].[sys_permission_role] ON
GO

INSERT INTO [dbo].[sys_permission_role] ([id], [role_id], [permission_id]) VALUES (N'5', N'1', N'5')
GO

INSERT INTO [dbo].[sys_permission_role] ([id], [role_id], [permission_id]) VALUES (N'6', N'1', N'6')
GO

INSERT INTO [dbo].[sys_permission_role] ([id], [role_id], [permission_id]) VALUES (N'7', N'1', N'7')
GO

INSERT INTO [dbo].[sys_permission_role] ([id], [role_id], [permission_id]) VALUES (N'8', N'1', N'8')
GO

INSERT INTO [dbo].[sys_permission_role] ([id], [role_id], [permission_id]) VALUES (N'9', N'2', N'5')
GO

INSERT INTO [dbo].[sys_permission_role] ([id], [role_id], [permission_id]) VALUES (N'10', N'2', N'6')
GO

INSERT INTO [dbo].[sys_permission_role] ([id], [role_id], [permission_id]) VALUES (N'11', N'2', N'8')
GO

SET IDENTITY_INSERT [dbo].[sys_permission_role] OFF
GO


-- ----------------------------
-- Table structure for sys_role
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[sys_role]') AND type IN ('U'))
	DROP TABLE [dbo].[sys_role]
GO

CREATE TABLE [dbo].[sys_role] (
  [id] int IDENTITY(1,1) NOT NULL,
  [name] nvarchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
  [description] nvarchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
GO

ALTER TABLE [dbo].[sys_role] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Records of [sys_role]
-- ----------------------------
SET IDENTITY_INSERT [dbo].[sys_role] ON
GO

INSERT INTO [dbo].[sys_role] ([id], [name], [description]) VALUES (N'1', N'ADMIN', N'系统管理员')
GO

INSERT INTO [dbo].[sys_role] ([id], [name], [description]) VALUES (N'2', N'USER', N'普通用户')
GO

SET IDENTITY_INSERT [dbo].[sys_role] OFF
GO


-- ----------------------------
-- Table structure for sys_role_user
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[sys_role_user]') AND type IN ('U'))
	DROP TABLE [dbo].[sys_role_user]
GO

CREATE TABLE [dbo].[sys_role_user] (
  [id] int IDENTITY(1,1) NOT NULL,
  [sys_user_id] int NOT NULL,
  [sys_role_id] int NOT NULL
)
GO

ALTER TABLE [dbo].[sys_role_user] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Records of [sys_role_user]
-- ----------------------------
SET IDENTITY_INSERT [dbo].[sys_role_user] ON
GO

INSERT INTO [dbo].[sys_role_user] ([id], [sys_user_id], [sys_role_id]) VALUES (N'1', N'15', N'1')
GO

INSERT INTO [dbo].[sys_role_user] ([id], [sys_user_id], [sys_role_id]) VALUES (N'2', N'16', N'2')
GO

INSERT INTO [dbo].[sys_role_user] ([id], [sys_user_id], [sys_role_id]) VALUES (N'3', N'37', N'2')
GO

SET IDENTITY_INSERT [dbo].[sys_role_user] OFF
GO


-- ----------------------------
-- Table structure for sys_user
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[sys_user]') AND type IN ('U'))
	DROP TABLE [dbo].[sys_user]
GO

CREATE TABLE [dbo].[sys_user] (
  [id] int IDENTITY(1,1) NOT NULL,
  [username] nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
  [password] nvarchar(200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
  [roles] nvarchar(200) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ('ROLE_USER') NULL,
  [nickname] nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  [tel] nvarchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
GO

ALTER TABLE [dbo].[sys_user] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Records of [sys_user]
-- ----------------------------
SET IDENTITY_INSERT [dbo].[sys_user] ON
GO

INSERT INTO [dbo].[sys_user] ([id], [username], [password], [roles], [nickname], [tel]) VALUES (N'15', N'admin', N'$2a$10$Ff1jDkrIZFVsqibRENPdy.LUNSrL.1BCkeheZBWYT4MHtqB8PcVK2', N'ROLE_ADMIN,ROLE_USER', N'管理员', N'18600000000')
GO

INSERT INTO [dbo].[sys_user] ([id], [username], [password], [roles], [nickname], [tel]) VALUES (N'16', N'gatc', N'$2a$10$PjTHAEAAUUHT2bm4wKtHh.qZthndTe.lmanu2It97enF.Xsjvg0L2', N'ROLE_ADMIN,ROLE_USER', N'gatc', N'18995588049')
GO

INSERT INTO [dbo].[sys_user] ([id], [username], [password], [roles], [nickname], [tel]) VALUES (N'37', N'huqi', N'$2a$10$39Epgo1Gx7vQscoPcbvlJOR2YV4vXQ03Jh7.rm8viHJE9hLSblqTS', N'ROLE_USER', N'huqi', N'18607187457')
GO

SET IDENTITY_INSERT [dbo].[sys_user] OFF
GO


-- ----------------------------
-- View structure for PermissionTreeView
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[PermissionTreeView]') AND type IN ('V'))
	DROP VIEW [dbo].[PermissionTreeView]
GO

CREATE VIEW [dbo].[PermissionTreeView] AS SELECT *,[dbo].[GetPermissionCountLayer]([id]) AS [layer],[dbo].[GetPermissionParentNode]([id]) AS [pid] FROM [dbo].[sys_permission]
GO


-- ----------------------------
-- Procedure structure for AddPermissionSubNodeByNode
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[AddPermissionSubNodeByNode]') AND type IN ('P', 'PC', 'RF', 'X'))
	DROP PROCEDURE[dbo].[AddPermissionSubNodeByNode]
GO

CREATE PROCEDURE [dbo].[AddPermissionSubNodeByNode]
(
	@pid INT,
	@name nvarchar(20),
	@url nvarchar(50),
	@description nvarchar(50)
)
AS
DECLARE @rgt INT
IF EXISTS (SELECT 1 FROM [dbo].[sys_permission] WHERE [id]=@pid)
BEGIN
	SET XACT_ABORT ON
	BEGIN TRANSACTION
	SELECT @rgt=[rgt] FROM [dbo].[sys_permission] WHERE [id]=@pid
	UPDATE [dbo].[sys_permission] SET [rgt]=[rgt]+2 WHERE [rgt]>=@rgt
	UPDATE [dbo].[sys_permission] SET [lft]=[lft]+2 WHERE [lft]>=@rgt
	INSERT INTO [dbo].[sys_permission] ([name],[url],[description],[lft],[rgt]) values (@name,@url,@description,@rgt,@rgt+1)    
	COMMIT TRANSACTION
	SET XACT_ABORT OFF
END
GO


-- ----------------------------
-- Procedure structure for DeletePermissionNode
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[DeletePermissionNode]') AND type IN ('P', 'PC', 'RF', 'X'))
	DROP PROCEDURE[dbo].[DeletePermissionNode]
GO

CREATE PROCEDURE [dbo].[DeletePermissionNode] 
	@id INT
AS
DECLARE @lft INT
DECLARE @rgt INT
IF EXISTS (SELECT 1 FROM [dbo].[sys_permission] WHERE [id]=@id)
BEGIN
	SET XACT_ABORT ON
	BEGIN TRANSACTION
	SELECT @lft=[lft],@rgt=[rgt] FROM [dbo].[sys_permission] WHERE [id]=@id
	DELETE FROM [dbo].[sys_permission] WHERE [lft]>=@lft AND [rgt]<=@rgt
	UPDATE [dbo].[sys_permission] SET [lft]=[lft]-(@rgt-@lft+1) WHERE [lft]>@lft
	UPDATE [dbo].[sys_permission] SET [rgt]=[rgt]-(@rgt-@lft+1) WHERE [rgt]>@rgt
	COMMIT TRANSACTION
	SET XACT_ABORT OFF    
END
GO


-- ----------------------------
-- Function structure for GetPermissionCountLayer
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[GetPermissionCountLayer]') AND type IN ('FN', 'FS', 'FT', 'IF', 'TF'))
	DROP FUNCTION[dbo].[GetPermissionCountLayer]
GO

CREATE FUNCTION [dbo].[GetPermissionCountLayer]
(    
	@id INT
)
RETURNS INT
AS
BEGIN
    DECLARE @result INT
    SET @result=0
    DECLARE @lft INT
    DECLARE @rgt INT
    IF EXISTS (SELECT 1 FROM [dbo].[sys_permission] WHERE [id]=@id)
    BEGIN
        SELECT @lft=[lft], @rgt=[rgt] FROM [dbo].[sys_permission] WHERE [id]=@id
        SELECT @result = COUNT(*) FROM [dbo].[sys_permission] WHERE [lft] <= @lft AND [rgt] >= @rgt
    END    
    RETURN @result
END
GO


-- ----------------------------
-- Procedure structure for GetPermissionListByNode
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[GetPermissionListByNode]') AND type IN ('P', 'PC', 'RF', 'X'))
	DROP PROCEDURE[dbo].[GetPermissionListByNode]
GO

CREATE PROCEDURE [dbo].[GetPermissionListByNode]
(
	@id INT
)
AS
DECLARE @lft INT
DECLARE @rgt INT
IF EXISTS (SELECT 1 FROM [dbo].[sys_permission] WHERE [id]=@id)
BEGIN
	SELECT @lft=[lft],@rgt=[rgt] FROM [dbo].[sys_permission] WHERE [id]=@id
	SELECT * FROM [dbo].[PermissionTreeView] WHERE [lft] BETWEEN @lft AND @rgt ORDER BY [lft] ASC
END
GO


-- ----------------------------
-- Procedure structure for MovePermissionNodeUp
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[MovePermissionNodeUp]') AND type IN ('P', 'PC', 'RF', 'X'))
	DROP PROCEDURE[dbo].[MovePermissionNodeUp]
GO

CREATE PROCEDURE [dbo].[MovePermissionNodeUp] 
    @id INT
AS
DECLARE @lft INT
DECLARE @rgt INT
DECLARE @layer INT
IF EXISTS (SELECT 1 FROM [dbo].[sys_permission] WHERE [id]=@id)
BEGIN
	SET XACT_ABORT ON
	BEGIN TRANSACTION
	SELECT @lft=[lft],@rgt=[rgt],@layer=[layer] FROM [dbo].[PermissionTreeView] WHERE [id]=@id
	IF EXISTS (SELECT * FROM [dbo].[PermissionTreeView] WHERE [rgt]=@lft-1 AND [layer]=@layer)
	BEGIN
		DECLARE @brother_lft INT
		DECLARE @brother_rgt INT
		SELECT @brother_lft=[lft],@brother_rgt=[rgt] FROM [dbo].[PermissionTreeView] WHERE [rgt]=@lft-1 AND [layer]=@layer
		UPDATE [dbo].[sys_permission] SET [lft]=[lft]-(@brother_rgt-@brother_lft+1) WHERE [lft]>=@lft AND [rgt]<=@rgt
		UPDATE [dbo].[sys_permission] SET [lft]=[lft]+(@rgt-@lft+1) WHERE [lft]>=@brother_lft AND [rgt]<=@brother_rgt
		UPDATE [dbo].[sys_permission] SET [rgt]=[rgt]-(@brother_rgt-@brother_lft+1) WHERE [rgt]>@brother_rgt AND [rgt]<=@rgt
		UPDATE [dbo].[sys_permission] SET [rgt]=[rgt]+(@rgt-@lft+1) WHERE [lft]>=@brother_lft+(@rgt-@lft+1) AND [rgt]<=@brother_rgt
	END
	COMMIT TRANSACTION
	SET XACT_ABORT OFF    
END
GO


-- ----------------------------
-- Function structure for GetPermissionParentNode
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[GetPermissionParentNode]') AND type IN ('FN', 'FS', 'FT', 'IF', 'TF'))
	DROP FUNCTION[dbo].[GetPermissionParentNode]
GO

CREATE FUNCTION [dbo].[GetPermissionParentNode]
(    
	@id INT
)
RETURNS INT
AS
BEGIN
    DECLARE @result INT
    SET @result=0
    DECLARE @lft INT
    DECLARE @rgt INT
    IF EXISTS (SELECT 1 FROM [dbo].[sys_permission] WHERE [id]=@id)
    BEGIN
        SELECT @lft=[lft],@rgt=[rgt] FROM [dbo].[sys_permission] WHERE [id]=@id
				IF(@lft = 1)
					SET @result = -1
				ELSE
					SELECT TOP(1)@result=[id] FROM [dbo].[sys_permission] WHERE [lft]<@lft AND [rgt]>@rgt ORDER BY [lft] DESC
    END    
    RETURN @result
END
GO


-- ----------------------------
-- Primary Key structure for table sys_permission
-- ----------------------------
ALTER TABLE [dbo].[sys_permission] ADD CONSTRAINT [PK_sys_permission] PRIMARY KEY CLUSTERED ([id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Primary Key structure for table sys_permission_role
-- ----------------------------
ALTER TABLE [dbo].[sys_permission_role] ADD CONSTRAINT [PK_sys_permission_role] PRIMARY KEY CLUSTERED ([id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Primary Key structure for table sys_role
-- ----------------------------
ALTER TABLE [dbo].[sys_role] ADD CONSTRAINT [PK__sys_role__3213E83F0A98298D] PRIMARY KEY CLUSTERED ([id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = OFF)  
ON [PRIMARY]
GO


-- ----------------------------
-- Primary Key structure for table sys_role_user
-- ----------------------------
ALTER TABLE [dbo].[sys_role_user] ADD CONSTRAINT [PK_sys_role_user] PRIMARY KEY CLUSTERED ([id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Primary Key structure for table sys_user
-- ----------------------------
ALTER TABLE [dbo].[sys_user] ADD CONSTRAINT [PK__userinfo__3213E83FFD539F20] PRIMARY KEY CLUSTERED ([id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Foreign Keys structure for table sys_permission_role
-- ----------------------------
ALTER TABLE [dbo].[sys_permission_role] ADD CONSTRAINT [FK_sys_permission_role_sys_role] FOREIGN KEY ([role_id]) REFERENCES [sys_role] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

ALTER TABLE [dbo].[sys_permission_role] ADD CONSTRAINT [FK_sys_permission_role_sys_permission] FOREIGN KEY ([permission_id]) REFERENCES [sys_permission] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO


-- ----------------------------
-- Foreign Keys structure for table sys_role_user
-- ----------------------------
ALTER TABLE [dbo].[sys_role_user] ADD CONSTRAINT [FK_sys_role_user_sys_user] FOREIGN KEY ([sys_user_id]) REFERENCES [sys_user] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

ALTER TABLE [dbo].[sys_role_user] ADD CONSTRAINT [FK_sys_role_user_sys_role] FOREIGN KEY ([sys_role_id]) REFERENCES [sys_role] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

